/**
 * 
 */
package tani.bnn.com.mbilling.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import tani.bnn.com.mbilling.core.DailySalePurchase;
import tani.bnn.com.mbilling.core.Product;
import tani.bnn.com.mbilling.core.ProductPurchaseDetail;
import tani.bnn.com.mbilling.core.ProductSaleDetail;
import tani.bnn.com.mbilling.core.QuantityPriceDetail;
import tani.bnn.com.mbilling.utility.ConnectionManager;
import tani.bnn.com.mbilling.utility.DateUtils;
import tani.bnn.com.mbilling.utility.MBillingConstant;

/**
 * @author netesh.thukral
 *
 */
public class DailySalePurchaseDAO {
	private Connection conn;
	private static String purchaseQry = " select cm.name comp_name, ct.name cat_name, pd.name prod_name, 'Purchase' type, pd.id prod_id," +
			"    sum(pud.quantity) Quantity, pud.price, date(pud.created_at) sale_purchase_date" +
			"    from companies cm" +
			"        inner join categories ct on cm.id = ct.company_id" +
			"        inner join products pd on cm.id = pd.company_id and ct.id = pd.category_id" +
			"        inner join purchase_details pud on pd.id = pud.product_id ";
	private static String purchaseGrpByQry = " group by cm.name, ct.name, pd.name, pud.price, pd.id, date(pud.created_at) ";
	private static String saleQry = " select cm.name comp_name, ct.name cat_name, pd.name prod_name, 'Sale' type, pd.id prod_id," +
			"    -sum(sd.quantity) Quantity, sd.sale_price price, date(sd.created_at) sale_purchase_date" +
			"    from companies cm" +
			"        inner join categories ct on cm.id = ct.company_id" +
			"        inner join products pd on cm.id = pd.company_id and ct.id = pd.category_id" +
			"        inner join sale_details sd on pd.id = sd.product_id ";
	private static String saleGrpByQry = " group by cm.name, ct.name, pd.name, pd.id, sd.sale_price, date(sd.created_at) ";
	private Connection getConnection() throws Exception{
		if(conn == null){
			conn = ConnectionManager.getNewConnection();
		}
		return conn;
	}
	public List<DailySalePurchase> getTodaysDailySalePurchase(){
		return getGivenDayDailySalePurchase(new Date(System.currentTimeMillis()));
	}
	public List<DailySalePurchase> getGivenDayDailySalePurchase(Date givenDay){
		List<DailySalePurchase> salePurchaseList = new ArrayList<DailySalePurchase>();
		try {
			Connection conn = getConnection();
			String strSQL =  DailySalePurchaseDAO.purchaseQry +
					"	where date(pud.created_at) = ? " + 
					DailySalePurchaseDAO.purchaseGrpByQry + " union all " + saleQry +
					"    where date(sd.created_at) = ? " + 
					DailySalePurchaseDAO.saleGrpByQry + " order by prod_id, type";
			PreparedStatement ps = conn.prepareStatement(strSQL);
			ps.setDate(1, givenDay);
			ps.setDate(2, givenDay);
			ResultSet rs = ps.executeQuery();
			populateDailySalePurchaseList(rs, salePurchaseList);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return salePurchaseList;
	}
	public List<DailySalePurchase> getAllDailySalePurchase(){
		List<DailySalePurchase> salePurchaseList = new ArrayList<DailySalePurchase>();
		try {
			Connection conn = getConnection();
			String strSQL = DailySalePurchaseDAO.purchaseQry + 
					"		where pd.deleted = 0 " +
					DailySalePurchaseDAO.purchaseGrpByQry +
					" union all " + DailySalePurchaseDAO.saleQry + 
					"		where pd.deleted = 0 " +
					DailySalePurchaseDAO.saleGrpByQry +
					" order by prod_id, type";
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(strSQL);
			populateDailySalePurchaseList(rs, salePurchaseList);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return salePurchaseList;
	}
	public List<DailySalePurchase> getGivenMonthDailySalePurchase(String monthYear){
		List<DailySalePurchase> salePurchaseList = new ArrayList<DailySalePurchase>();
		Date dayBegin = null;
		Date dayEnd = null;
		try {
			dayBegin = new Date(DateUtils.getMonthStart(monthYear).getTime());
			dayEnd = new Date(DateUtils.getMonthEnd(monthYear).getTime());
			Connection conn = getConnection();
			String strSQL = DailySalePurchaseDAO.purchaseQry + 
					"	where date(pud.created_at) >= ? " +
					"		and date(pud.created_at) < ? " +
					DailySalePurchaseDAO.purchaseGrpByQry + " union all " +
					DailySalePurchaseDAO.saleQry +
					"    where date(sd.created_at) >= ? " +
					"		and datedate_format(sd.created_at) < ? " +
					DailySalePurchaseDAO.saleGrpByQry + " order by prod_id, type";
			PreparedStatement ps = conn.prepareStatement(strSQL);
			ps.setDate(1, dayBegin);
			ps.setDate(2, dayEnd);
			ps.setDate(3, dayBegin);
			ps.setDate(4, dayEnd);
			ResultSet rs = ps.executeQuery();
			populateDailySalePurchaseList(rs, salePurchaseList);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return salePurchaseList;
	}
	private void populateDailySalePurchaseList(ResultSet rs, List<DailySalePurchase> salePurchaseList) throws SQLException{
		while (rs.next()){
			DailySalePurchase dSalePurchase = new DailySalePurchase();
			dSalePurchase.setId(rs.getInt("prod_id"));
			dSalePurchase.setName(rs.getString("prod_name"));
			dSalePurchase.setCategoryName(rs.getString("cat_name"));
			dSalePurchase.setCompanyName(rs.getString("comp_name"));
//			dSalePurchase.setModel(rs.getString("model"));
//			dSalePurchase.setSearchCode(rs.getString("search_code"));
			dSalePurchase.setType(rs.getString("type"));
			dSalePurchase.setQuantity(rs.getInt("Quantity"));
			dSalePurchase.setPrice(rs.getInt("price"));
			dSalePurchase.setSopDate(rs.getDate("sale_purchase_date"));
			salePurchaseList.add(dSalePurchase);
		}
		
	}
	public List<Product> getAllProductList(){
		List<Product> productList = new ArrayList<Product>();
		try {
			Connection conn = getConnection();
			String strSQL = DailySalePurchaseDAO.purchaseQry + DailySalePurchaseDAO.purchaseGrpByQry +
					" union all " + DailySalePurchaseDAO.saleQry + DailySalePurchaseDAO.saleGrpByQry +
					" order by prod_id, sale_purchase_date, type";
			Statement stmt = conn.createStatement();
			stmt.setFetchSize(1000);
			ResultSet rs = stmt.executeQuery(strSQL);
			populateProductList(rs, productList);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if(!productList.isEmpty()){
			for(Product product: productList){
				product.setProfitLossByDate();
			}
		}
		return productList;
	}
	private void populateProductList(ResultSet rs, List<Product> productList) throws SQLException{
		int oldProdId = 0;
		int newProdId = 0;
		Product product = null;
		while (rs.next()){
			newProdId = rs.getInt("prod_id");
			if(newProdId != oldProdId){
				oldProdId = newProdId;
				product = new Product();
				productList.add(product);
				product.setId(newProdId);
				product.setName(rs.getString("prod_name"));
				product.setCategoryName(rs.getString("cat_name"));
				product.setCompanyName(rs.getString("comp_name"));
//				product.setModel(rs.getString("model"));
//				product.setSearchCode(rs.getString("search_code"));
			}
			QuantityPriceDetail details = null;
			if(MBillingConstant.SALE.equals(rs.getString("type"))){
				details = new ProductSaleDetail();
				product.getSales().add((ProductSaleDetail)details);
			} else {
				details = new ProductPurchaseDetail();
				product.getPurchases().add((ProductPurchaseDetail)details);
			}
			details.setQuantity(rs.getInt("Quantity"));
			details.setPrice(rs.getInt("price"));
			details.setSopDate(rs.getDate("sale_purchase_date"));
		}
	}
	public List<Product> getAllProductList(String beforeDate){
		List<Product> productList = new ArrayList<Product>();
		Date date = null;
		try {
			date = new Date(DateUtils.getDateValue(beforeDate, DateUtils.DATE_MONTH_FORMAT).getTime());
			Connection conn = getConnection();
			String strSQL = DailySalePurchaseDAO.purchaseQry + "	where date(pud.created_at) < ? " + 
					DailySalePurchaseDAO.purchaseGrpByQry + " union all " + 
					DailySalePurchaseDAO.saleQry + "    where date(sd.created_at) < ? " + 
					DailySalePurchaseDAO.saleGrpByQry + " order by prod_id, sale_purchase_date, type";
			PreparedStatement stmt = conn.prepareStatement(strSQL);
			stmt.setFetchSize(1000);
			stmt.setDate(1, date);
			stmt.setDate(2, date);
			ResultSet rs = stmt.executeQuery();
			populateProductList(rs, productList);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if(!productList.isEmpty()){
			for(Product product: productList){
				product.setProfitLossByDate();
			}
		}
		return productList;
	}
	@SuppressWarnings("unused")
	public static void main(String[] args){
		try{
			Date dt = new Date(DateUtils.getDateValue("18-08-2012").getTime());
			DailySalePurchaseDAO spDAO = new DailySalePurchaseDAO();
			List<Product> list = spDAO.getAllProductList();
			List<DailySalePurchase> salePurchaseList = spDAO.getGivenDayDailySalePurchase(dt);
			List<DailySalePurchase> monthlysalePurchaseList = spDAO.getGivenMonthDailySalePurchase("08-2012");
			List<DailySalePurchase> allsalePurchaseList = spDAO.getAllDailySalePurchase();
		} catch (Exception ex){
			
		}
	}
}